// PurchaseDAOImpl.java - 数据访问实现
package com.example.electricbicycle.dao;

import com.example.electricbicycle.po.PurchaseOrder;
import com.example.electricbicycle.po.Vehicle;
import utils.DBUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class PurchaseDAOImpl implements PurchaseDAO {

    @Override
    public boolean addPurchaseOrder(PurchaseOrder order) {
        String sql = "INSERT INTO purchase_order (user_id, v_id, quantity, status) VALUES (?, ?, ?, ?)";
        try (Connection conn = DBUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, order.getUser_id());
            pstmt.setInt(2, order.getV_id());
            pstmt.setInt(3, order.getQuantity());
            pstmt.setInt(4, order.getStatus());

            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    @Override
    public boolean updatePurchaseOrder(PurchaseOrder order) {
        if (!isOrderEditable(order.getId())) {
            return false;
        }

        String sql = "UPDATE purchase_order SET v_id = ?, quantity = ? WHERE id = ?";
        try (Connection conn = DBUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, order.getV_id());
            pstmt.setInt(2, order.getQuantity());
            pstmt.setInt(3, order.getId());

            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    @Override
    public boolean deletePurchaseOrder(int orderId) {
        if (!isOrderEditable(orderId)) {
            return false;
        }

        String sql = "DELETE FROM purchase_order WHERE id = ?";
        try (Connection conn = DBUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, orderId);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    @Override
    public PurchaseOrder getPurchaseOrderById(int orderId) {
        String sql = "SELECT * FROM purchase_order WHERE id = ?";
        try (Connection conn = DBUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, orderId);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return mapResultSetToPurchaseOrder(rs);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<PurchaseOrder> getPurchaseOrdersBySalesman(int salesmanId) {
        List<PurchaseOrder> orders = new ArrayList<>();
        String sql = "SELECT * FROM purchase_order WHERE user_id = ? ORDER BY create_time DESC";
        try (Connection conn = DBUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, salesmanId);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    PurchaseOrder order = mapResultSetToPurchaseOrder(rs);
                    Vehicle vehicle = getVehicleById(order.getV_id());
                    if (vehicle != null) {
                        order.setVehicle(vehicle);
                    }

                    orders.add(order);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return orders;
    }

    @Override
    public boolean isOrderEditable(int orderId) {
        String sql = "SELECT status FROM purchase_order WHERE id = ?";
        try (Connection conn = DBUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, orderId);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt("status") == 0; // 0=未审核
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public List<Vehicle> getAllVehicles() {
        List<Vehicle> vehicles = new ArrayList<>();
        String sql = "SELECT * FROM vehicle_info";
        try (Connection conn = DBUtils.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                vehicles.add(mapResultSetToVehicle(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return vehicles;
    }

    @Override
    public Vehicle getVehicleById(int id) {
        String sql = "SELECT * FROM vehicle_info WHERE id = ?";
        try (Connection conn = DBUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, id);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return mapResultSetToVehicle(rs);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int getTotalOrderCount(int salesmanId) throws SQLException {
        String sql = "SELECT COUNT(*) FROM purchase_order WHERE user_id = ?";
        try (Connection conn = DBUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, salesmanId);
            try (ResultSet rs = pstmt.executeQuery()) {
                return rs.next() ? rs.getInt(1) : 0;
            }
        }
    }

    @Override
    public int getPendingOrderCount(int salesmanId) throws SQLException {
        String sql = "SELECT COUNT(*) FROM purchase_order WHERE user_id = ? AND status = 0";
        try (Connection conn = DBUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, salesmanId);
            try (ResultSet rs = pstmt.executeQuery()) {
                return rs.next() ? rs.getInt(1) : 0;
            }
        }
    }

    @Override
    public int getApprovedOrderCount(int salesmanId) throws SQLException {
        String sql = "SELECT COUNT(*) FROM purchase_order WHERE user_id = ? AND status = 1";
        try (Connection conn = DBUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, salesmanId);
            try (ResultSet rs = pstmt.executeQuery()) {
                return rs.next() ? rs.getInt(1) : 0;
            }
        }
    }

    @Override
    public List<PurchaseOrder> getFilteredOrders(int salesmanId, String type) throws SQLException {
        List<PurchaseOrder> orders = new ArrayList<>();
        String sql;

        switch (type) {
            case "pending":
                sql = "SELECT * FROM purchase_order WHERE user_id = ? AND status = 0 ORDER BY create_time DESC";
                break;
            case "approved":
                sql = "SELECT * FROM purchase_order WHERE user_id = ? AND status = 1 ORDER BY create_time DESC";
                break;
            default:
                sql = "SELECT * FROM purchase_order WHERE user_id = ? ORDER BY create_time DESC";
        }

        try (Connection conn = DBUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, salesmanId);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    PurchaseOrder order = mapResultSetToPurchaseOrder(rs);
                    Vehicle vehicle = getVehicleById(order.getV_id());
                    if (vehicle != null) {
                        order.setVehicle(vehicle);
                    }

                    orders.add(order);
                }
            }
        }
        return orders;
    }

    @Override
    public List<PurchaseOrder> getAllOrdersForSalesman(int salesmanId) {
        return getPurchaseOrdersBySalesman(salesmanId);
    }

    private PurchaseOrder mapResultSetToPurchaseOrder(ResultSet rs) throws SQLException {
        PurchaseOrder order = new PurchaseOrder();
        order.setId(rs.getInt("id"));
        order.setUser_id(rs.getInt("user_id"));
        order.setV_id(rs.getInt("v_id"));
        order.setQuantity(rs.getInt("quantity"));
        order.setStatus(rs.getInt("status"));
        order.setCreateTime(rs.getTimestamp("create_time"));
        return order;
    }

    private Vehicle mapResultSetToVehicle(ResultSet rs) throws SQLException {
        Vehicle vehicle = new Vehicle();
//        vehicle.setId(rs.getInt("v_id"));
        vehicle.setId(rs.getInt("id"));
        vehicle.setBrand(rs.getString("brand"));
        vehicle.setModel(rs.getString("model"));
        vehicle.setColor(rs.getString("color"));
        vehicle.setPrice(rs.getDouble("price"));
        vehicle.setProductionYear(rs.getInt("production_year"));
        vehicle.setPic(rs.getString("pic"));
        return vehicle;
    }

    @Override
    public boolean addVehicle(Vehicle vehicle) {
        String sql = "INSERT INTO vehicle_info (brand, model, color, price, production_year, pic) " +
                "VALUES (?, ?, ?, ?, ?, ?)";
        try (Connection conn = DBUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, vehicle.getBrand());
            pstmt.setString(2, vehicle.getModel());
            pstmt.setString(3, vehicle.getColor());
            pstmt.setDouble(4, vehicle.getPrice());
            pstmt.setInt(5, vehicle.getProductionYear());
            pstmt.setString(6, vehicle.getPic());

            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    @Override
    public boolean updateVehicle(Vehicle vehicle) {
        String sql = "UPDATE vehicle_info SET brand=?, model=?, color=?, price=?, production_year=?, pic=? " +
                "WHERE id=?";
        try (Connection conn = DBUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, vehicle.getBrand());
            pstmt.setString(2, vehicle.getModel());
            pstmt.setString(3, vehicle.getColor());
            pstmt.setDouble(4, vehicle.getPrice());
            pstmt.setInt(5, vehicle.getProductionYear());
            pstmt.setString(6, vehicle.getPic());
            pstmt.setInt(7, vehicle.getId());

            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    @Override
    public boolean deleteVehicle(int vehicleId) {
        // 先检查该车辆是否已被使用
        String checkSql = "SELECT COUNT(*) FROM purchase_order WHERE v_id=?";
        try (Connection conn = DBUtils.getConnection();
             PreparedStatement checkStmt = conn.prepareStatement(checkSql)) {

            checkStmt.setInt(1, vehicleId);
            try (ResultSet rs = checkStmt.executeQuery()) {
                if (rs.next() && rs.getInt(1) > 0) {
                    return false; // 车辆已被使用，不能删除
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }

        // 删除车辆
        String deleteSql = "DELETE FROM vehicle_info WHERE id=?";
        try (Connection conn = DBUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(deleteSql)) {

            pstmt.setInt(1, vehicleId);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

}